﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLServerHealthCheck.Model;

namespace SQLServerHealthCheck.SQLServer.IssueTypes
{
    public class SecurityCheckTooManySysAdminLogins : IssueTypeCheckBase
    {
        public SecurityCheckTooManySysAdminLogins()
        {
            IssueTypeId = 2003;
        }

        public override void Check()
        {
            HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]);
            Issue issue;

            /*
             * This rule checks if there are more than 3 Logins with SysAdmin Privileges 
             */
            var QrySysAdmin = from lsr in dc.LoginServerRoles
                               from  l in dc.Logins
                              where ((lsr.ServerRoleId == 9) && (lsr.Login.Name != "sa") && (lsr.Login.Name != @"NT AUTHORITY\SYSTEM"))

                              group lsr by lsr.Login.ServiceId into groupserviceIDs

                              select new { serviceID = groupserviceIDs.Key, count = groupserviceIDs.Count()};
                   
            
            
            foreach (var item in QrySysAdmin)
            {
                if (item.count > 3)
                {
                    IssueType issueType = dc.IssueTypes.FirstOrDefault(i => i.IssueTypeId == IssueTypeId);
                    //Create new Issue
                    issue = new Issue();


                    issue.IssueType = issueType;
                    issue.TargetId = item.serviceID;
                    
                    //Create New Service Table object to retrive name of Service intance
                    Service service = dc.Services.FirstOrDefault(sn => sn.ServerID == item.serviceID);                 
                    
                    issue.Description = issueType.Description.Replace("{InstanceName}", service.InstanceName);

                    dc.Issues.InsertOnSubmit(issue);
                }
            }
            dc.SubmitChanges();
        }

    }

}
